﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelSupplierObject
    {
        public const string sql = @"
SELECT convert(nchar(20),DictionaryPartners.Name) as Поставщик__топлива
	,convert(nchar(20),COALESCE(report.SumOplat,0)) as Оплачено__грн
	,convert(nchar(20),COALESCE(report.SumDolg,0)) as Заправлено__грн
	,convert(nchar(20),COALESCE(report.SumOplat - report.SumDolg,0)) as Баланс__грн
	,convert(nchar(20),DictionaryProject.Name) as Проект
FROM
(
	SELECT r1.Partner as Partner
		,COALESCE(r1.SumDolg,0) as SumDolg
		,COALESCE(t1.SumOplat,0) as SumOplat
		,r1.Project as Project
	FROM
		(SELECT [Refueling].[FuelSupplier] as Partner
			   ,Sum(COALESCE([Refueling].[Price],0)) as SumDolg
			   ,[Refueling].[Project] as Project
		FROM   [Refueling]
		WHERE  [Refueling].[FuelOperType] = 2
		Group by [Refueling].[FuelSupplier], [Refueling].[Project]) as r1
	LEFT JOIN
		(SELECT [Transactions].[Partner] as Partner
			   ,sum(COALESCE([Summa],0)) as SumOplat
			   ,[Transactions].[Project] as Project
		FROM   [Transactions]
		WHERE  [Transactions].[Type] = 2 and [Transactions].[Category] in (select id from DictionaryCategories where Fuel2 = 1)
		Group by [Transactions].[Partner], [Transactions].[Project], [Transactions].[Currency]) as t1
	ON r1.Partner = t1.Partner and r1.Project = t1.Project
UNION ALL
	SELECT t1.Partner as Partner
		,COALESCE(r1.SumDolg,0) as SumDolg
		,COALESCE(t1.SumOplat,0) as SumOplat
		,t1.Project as Project
	FROM
			(SELECT [Transactions].[Partner] as Partner
			   ,sum(COALESCE([Summa],0)) as SumOplat
			   ,[Transactions].[Project] as Project
		FROM   [Transactions]
		WHERE  [Transactions].[Type] = 2 and [Transactions].[Category] in (select id from DictionaryCategories where Fuel2 = 1)
		Group by [Transactions].[Partner], [Transactions].[Project]) as t1
	LEFT JOIN
		(SELECT [Refueling].[FuelSupplier] as Partner
			   ,Sum(COALESCE([Refueling].[Price],0)) as SumDolg
			   ,[Refueling].[Project] as Project
		FROM   [Refueling]
		WHERE  [Refueling].[FuelOperType] = 2
		Group by [Refueling].[FuelSupplier], [Refueling].[Project]) as r1
	ON t1.Partner = r1.Partner and t1.Project =r1.Project
	WHERE r1.Partner is null and r1.SumDolg is null and r1.Project is null
) as report

INNER JOIN DictionaryPartners on DictionaryPartners.ID = report.Partner
INNER JOIN DictionaryProject on DictionaryProject.ID = report.Project

ORDER BY Поставщик__топлива, Баланс__грн, Проект
";
        public string Поставщик__топлива { get; set; }
        public string Оплачено__грн { get; set; }
        public string Заправлено__грн { get; set; }
        public string Баланс__грн { get; set; }
        public string Проект { get; set; }
    }
}
